"""
py-opengauss <https://gitee.com/wjmcat/py-opengauss>

Authors:    Jingjing WU (吴京京) <https://gitee.com/wjmcat/>

2021-now @ Jingjing WU (吴京京)

Licensed under the Apache License, Version 2.0 (the 'License');
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an 'AS IS' BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
"""
from __future__ import annotations

import os
import sys
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))

import random

from psycopg2 import connect
from psycopg2.extensions import connection
from faker import Faker
from examples import get_logger


logger = get_logger()
table_name = "test_table_one"


def create_conn() -> connection:
    """get connection from envrionment variable by the conn factory

    Returns:
        [type]: the psycopg2's connection object
    """
    env = os.environ
    params = {
        'database': env.get('OG_DATABASE', 'opengauss'),
        'user': env.get('OG_USER', 'gaussdb'),
        'password': env.get('OG_PASSWORD', 'Secretpassword@123'),
        'host': env.get('OG_HOST', '127.0.0.1'),
        'port': env.get('OG_PORT', 5432)
    }
    conn: connection = connect(**params)
    return conn

def create_table(conn):
    """check and create table by example

    Args:
        table_name (str): the name of the table
        corsor (type): the corsor type to get into operation with db
    """
    sql = f"""SELECT EXISTS 
        (
            SELECT 1 
            FROM pg_tables
            WHERE tablename = '{table_name}'
        );"""
    with conn:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchone()
            if not result[0]:
                logger.info(f'creating table<{table_name}>')
                sql = f"""CREATE TABLE {table_name} (id serial PRIMARY KEY, name varchar, course varchar, grade integer);"""
                result = cursor.execute(sql)
                conn.commit()
            
def insert_data(conn) -> int:
    """insert faker data

    Args:
        cnn ([type]): the connection object to the databse
    """
    faker = Faker(locale=['zh-cn'])
    sql = f"insert into {table_name} (name, course, grade) values (%s,%s,%s) RETURNING *;"
    with conn:
        with conn.cursor() as cursor:
            age = random.randint(20, 80)
            result = cursor.execute(sql, (faker.name(), faker.name(), age))
            result = cursor.fetchone()
            logger.info(f'add data<{result}> to the databse')
            conn.commit()
    return result[0] if result else None

def get_data_by_id(conn, id: int):
    """fetch data by id

    Args:
        conn ([type]): the connection object
        id (int): the primary key of the table

    Returns:
        [type]: the tuple data of the table
    """
    sql = f"select * from {table_name} where id = %s;"
    with conn:
        with conn.cursor() as cursor:
            cursor.execute(sql, (id,))
            result = cursor.fetchone()
            logger.info(f'select data<{result}> from databse')
    return result

def delete_data_by_id(conn, id: int):
    """delete data by primary key

    Args:
        conn ([type]): the connection object
        id (int): the primary key of the table
    """
    sql = f"delete from {table_name} where id = %s;"
    with conn:
        with conn.cursor() as cursor:
            cursor.execute(sql, (id,))
            logger.info(f'delete data from databse by id<{id}>')

if __name__ == "__main__":
    conn = create_conn()
    with conn:
        create_table(conn)

        id = insert_data(conn)
        assert id is not None

        data = get_data_by_id(conn, id)
        assert data is not None

        delete_data_by_id(conn, id)
        assert get_data_by_id(conn, id) == None
        
    conn.close()
